"""
Case Type   : 基础算子性能提升-快速参数化增加视图查询功能
Case Name   : 执行不支持参数化的iud语句（含有不支持的关键字，语法，函数，子查询等），查看视图没有缓存记录
Create At   : 2025-10-17
Owner       : sungang14
Description :
    1、set enable_query_parameterization = on;
    2、执行create table语句
    3、执行不支持参数化的iud语句，查看视图
Expect      :
    1、设置成功
    2、成功
    3、没有缓存记录
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.show_query_parameterization = 'show enable_query_parameterization;'
        self.set_query_parameterization = "enable_query_parameterization = ON"
        self.set_query_parameterization_01 = "enable_query_parameterization = OFF"
        self.table_name = f"query_parameterization_views_case0005"
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"create table {self.table_name}(id int,v1 int,v2 int,v3 varchar(255));"
        self.prepare_data_02 = f"insert into {self.table_name} values(1,1,2,'abc') returning id, v1, v2, v3;" \
                               f"UPDATE {self.table_name} SET id = 7 WHERE id = 1 returning id;" \
                               f"delete from {self.table_name} where id =7 returning id, v1, v2, v3;" \
                               f"select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"
        text = '--step1:开启enable_query_parameterization参数;expect:开启成功--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization)
        logger.info(set_cmd1)

    def test_slowsql(self):
        text = '--step2:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step3:执行不支持参数化的iud语句,查看query_parameterization_views;expect:没有缓存记录--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(f'INSERT 0 1', data_cmd, '执行失败' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn(f'0 rows', data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization_01)
        logger.info(set_cmd1)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
